import pymysql


def test1():
        

    # 连接数据库
    conn = pymysql.connect(
        host='localhost',  # 主机名
        user='root',  # 用户名
        password='root',  # 密码
        database='test',  # 数据库名称
        port=3306
    )

    # 创建游标
    cursor = conn.cursor()

    # 执行SQL查询
    sql = "SHOW COLUMNS FROM competition"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 解析结果
    table_structure = []
    for row in result:
        field_name = row[0]
        data_type = row[1]
        is_nullable = row[2]
        key = row[3]
        extra = row[4]
        table_structure.append((field_name, data_type, is_nullable, key, extra))



    # 格式化并打印表结构信息
    print("Field\t\tData Type\tNullable\tKey\tExtra")
    print("----------------------------------------------------")
    for field_name, data_type, is_nullable, key, extra in table_structure:
        print(f"{field_name}")

    cursor.close()
    conn.close()





def test2(table_name):
    conn = pymysql.connect(host="127.0.0.1", user="root",password="root", db="test", autocommit=True)
    cur = conn.cursor()
    
    sql = f"select COLUMN_NAME from information_schema.COLUMNS where table_name = '{table_name}'"
    cur.execute(sql)

    for field in cur.fetchall():
        print(field[0])
    
    cur.close()
    conn.close()




def show(table_name):
    conn = pymysql.connect(host="127.0.0.1", user="root", password="root", db="test", autocommit=True)
    cur = conn.cursor()


    # 执行SQL查询语句获取表的结构信息
    query = f"DESCRIBE {table_name}"
    cur.execute(query)

    # 获取查询结果
    result = cur.fetchall()

    # 解析表的结构信息，提取字段的格式
    field_formats = {}
    for row in result:
        field_name = row[0]
        field_type = row[1]
        field_formats[field_name] = field_type

    # 输出字段格式
    for field_name, field_type in field_formats.items():
        print(f"{field_name}: {field_type}")


    cur.close()
    conn.close()




def show_tables(database):
    conn = pymysql.connect(host="127.0.0.1", user="root", password="root", db=database, autocommit=True)
    cur = conn.cursor()

    sql = f'show tables from {database}'
    rows = cur.execute(sql)
    print(f'一共有 {rows} 张表')

    # result = []
    for d in cur.fetchall():
       print(d[0])

    cur.close()
    conn.close()




# test2('competition')

# test3('competition')
    
show('competition')


# show_tables('test')




# CREATE TABLE `datasource` (
#   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
#   `name` varchar(512) NOT NULL DEFAULT '' COMMENT '数据源名称',
#   `host` varchar(64) NOT NULL DEFAULT '' COMMENT '主机',
#   `port` varchar(8) NOT NULL DEFAULT '' COMMENT '端口',
#   `username` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名',
# 	`password` varchar(256) NOT NULL DEFAULT '' COMMENT '密码',
#   `db` varchar(64) NOT NULL DEFAULT '' COMMENT '数据库',
	
#   `create_time` bigint(20) unsigned NOT NULL COMMENT '创建时间',
#   `update_time` bigint(20) unsigned NOT NULL COMMENT '更新时间',
#   PRIMARY KEY (`id`) USING BTREE
# ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='数据源表';
